<?php

namespace app\admin\controller\scanwork;

use app\common\controller\Backend;
use think\Db;

/**
 * BI报表
 * @icon fa fa-bar-chart
 */
class Bi extends Backend
{
    /**
     * 综合看板
     */
    public function dashboard()
    {
        if ($this->request->isAjax()) {
            $today = date('Y-m-d');
            
            $data = [
                'production' => Db::query("SELECT * FROM v_production_overview WHERE stat_date = '{$today}'"),
                'quality' => Db::query("SELECT * FROM v_quality_analysis WHERE stat_date = '{$today}'"),
                'orders' => Db::name('scanwork_order')->where('status', '<>', 2)->count(),
                'plans' => Db::name('scanwork_production_plan')->where('status', 2)->count()
            ];
            
            return json(['code' => 1, 'data' => $data]);
        }
        return $this->view->fetch();
    }

    /**
     * 生产效率
     */
    public function production_efficiency()
    {
        $startDate = $this->request->get('start_date', date('Y-m-01'));
        $endDate = $this->request->get('end_date', date('Y-m-d'));

        if ($this->request->isAjax()) {
            // 检查是否需要生成数据
            $hasData = Db::name('scanwork_production_efficiency')
                ->where('stat_date', 'between', [$startDate, $endDate])
                ->count();
            
            if ($hasData == 0) {
                $this->generateProductionEfficiencyData($startDate, $endDate);
            }
            
            $total = Db::name('scanwork_production_efficiency')->count();
            
            $list = Db::name('scanwork_production_efficiency')
                ->order('stat_date', 'desc')
                ->select();
            
            return json(['total' => $total, 'rows' => $list]);
        }
        return $this->view->fetch();
    }

    /**
     * 质量分析
     */
    public function quality_analysis()
    {
        $startDate = $this->request->get('start_date', date('Y-m-01'));
        $endDate = $this->request->get('end_date', date('Y-m-d'));

        if ($this->request->isAjax()) {
            // 检查是否需要生成数据
            $hasData = Db::name('scanwork_quality_statistics')
                ->where('stat_date', 'between', [$startDate, $endDate])
                ->count();
            
            if ($hasData == 0) {
                $this->generateQualityAnalysisData($startDate, $endDate);
            }
            
            $total = Db::name('scanwork_quality_statistics')->count();
            
            $list = Db::name('scanwork_quality_statistics')
                ->order('stat_date', 'desc')
                ->select();
            
            return json(['total' => $total, 'rows' => $list]);
        }
        return $this->view->fetch();
    }

    /**
     * 物料损耗
     */
    public function material_loss()
    {
        $startDate = $this->request->get('start_date', date('Y-m-01'));
        $endDate = $this->request->get('end_date', date('Y-m-d'));

        if ($this->request->isAjax()) {
            // 检查是否需要生成数据
            $hasData = Db::name('scanwork_material_loss')
                ->where('stat_date', 'between', [$startDate, $endDate])
                ->count();
            
            if ($hasData == 0) {
                $this->generateMaterialLossData($startDate, $endDate);
            }
            
            $total = Db::name('scanwork_material_loss')
                ->alias('l')
                ->join('scanwork_material m', 'l.material_id = m.id')
                ->count();
            
            $list = Db::name('scanwork_material_loss')
                ->alias('l')
                ->join('scanwork_material m', 'l.material_id = m.id')
                ->field('l.*, m.name as material_name, m.code as material_code')
                ->order('l.loss_cost', 'desc')
                ->select();
            
            return json(['total' => $total, 'rows' => $list]);
        }
        return $this->view->fetch();
    }

    /**
     * 员工绩效
     */
    public function employee_performance()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if ($this->request->isAjax()) {
            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();

            // 获取查询参数，默认查询最近30天
            $startDate = $this->request->get('start_date', date('Y-m-d', strtotime('-90 days')));
            $endDate = $this->request->get('end_date', date('Y-m-d'));
            
            // 先尝试从统计表获取数据
            $list = Db::name('scanwork_employee_performance')
                ->alias('p')
                ->join('user u', 'p.employee_id = u.id')
                ->where($where)
                ->where('p.stat_date', 'between', [$startDate, $endDate])
                ->field('p.*, u.nickname as employee_name, u.username as employee_code')
                ->order($sort, $order)
                ->limit($offset, $limit)
                ->select();
            
            // 检查当前查询范围内是否需要重新生成数据（检查是否有质量评分和效率评分）
            $hasQualityData = Db::name('scanwork_employee_performance')
                ->where('stat_date', 'between', [$startDate, $endDate])
                ->where('quality_rate', '>', 0)
                ->where('efficiency_rate', '>', 0)
                ->count();
            
            // 记录调试信息
            \think\Log::write("Debug: hasQualityData={$hasQualityData}, list_count=" . count($list), 'debug');
            
            if ($hasQualityData == 0) {
                // 先检查报工表是否有数据
                $reportCount = Db::name('scanwork_report')->count();
                $allocationCount = Db::name('scanwork_allocation')->count();
                
                // 记录调试信息
                \think\Log::write("Debug: report_count={$reportCount}, allocation_count={$allocationCount}, hasQualityData={$hasQualityData}", 'debug');
                
                if ($reportCount > 0) {
                    // 清理所有旧的绩效数据，强制重新生成
                    Db::name('scanwork_employee_performance')->where('1=1')->delete();
                    
                    $this->generateEmployeePerformanceData($startDate, $endDate);
                    
                    // 重新查询统计表
                    $list = Db::name('scanwork_employee_performance')
                        ->alias('p')
                        ->join('user u', 'p.employee_id = u.id')
                        ->where($where)
                        ->where('p.stat_date', 'between', [$startDate, $endDate])
                        ->field('p.*, u.nickname as employee_name, u.username as employee_code')
                        ->order($sort, $order)
                        ->limit($offset, $limit)
                        ->select();
                }
            }
            
            $total = Db::name('scanwork_employee_performance')
                ->alias('p')
                ->join('user u', 'p.employee_id = u.id')
                ->where($where)
                ->where('p.stat_date', 'between', [$startDate, $endDate])
                ->count();
            
            // 记录调试信息
            \think\Log::write("Debug: Query date range: {$startDate} to {$endDate}, total records: {$total}", 'debug');

            $result = array("total" => $total, "rows" => $list);
            return json($result);
        }
        return $this->view->fetch();
    }
    
    /**
     * 生成员工绩效统计数据
     */
    private function generateEmployeePerformanceData($startDate, $endDate)
    {
        // 从报工表通过分配表关联员工信息统计员工绩效数据
        $reportData = Db::name('scanwork_report')
            ->alias('r')
            ->join('scanwork_allocation a', 'r.allocation_id = a.id')
            ->join('user u', 'a.user_id = u.id')
            ->where('r.status', 1) // 只统计已确认的报工
            ->where('FROM_UNIXTIME(r.report_time)', 'between', [$startDate, $endDate])
            ->field('
                a.user_id as employee_id,
                DATE(FROM_UNIXTIME(r.report_time)) as stat_date,
                COUNT(*) as completed_tasks,
                SUM(r.quantity) as output_quantity,
                SUM(r.wage) as total_wage,
                AVG(r.wage/r.quantity) as avg_price
            ')
            ->group('a.user_id, DATE(FROM_UNIXTIME(r.report_time))')
            ->select();
        
        // 记录调试信息
        \think\Log::write("Debug: Found " . count($reportData) . " report records for date range {$startDate} to {$endDate}", 'debug');
        
        foreach ($reportData as $data) {
            // 检查是否已存在该日期的统计数据
            $exists = Db::name('scanwork_employee_performance')
                ->where('employee_id', $data['employee_id'])
                ->where('stat_date', $data['stat_date'])
                ->find();
            
            if (!$exists) {
                // 计算绩效评分（可以根据实际业务规则调整）
                $performanceScore = $this->calculatePerformanceScore($data);
                
                // 计算质量评分和效率评分
                $qualityRate = $this->calculateQualityRate($data);
                $efficiencyRate = $this->calculateEfficiencyRate($data);
                
                // 插入统计数据
                $result = Db::name('scanwork_employee_performance')->insert([
                    'stat_date' => $data['stat_date'],
                    'stat_type' => 'daily',
                    'employee_id' => $data['employee_id'],
                    'completed_tasks' => $data['completed_tasks'],
                    'output_quantity' => $data['output_quantity'],
                    'total_wage' => $data['total_wage'],
                    'quality_rate' => $qualityRate,
                    'efficiency_rate' => $efficiencyRate,
                    'performance_score' => $performanceScore,
                    'createtime' => time()
                ]);
                
                // 记录插入结果
                \think\Log::write("Debug: Inserted performance data for employee {$data['employee_id']} on {$data['stat_date']}, result: " . ($result ? 'success' : 'failed'), 'debug');
            } else {
                \think\Log::write("Debug: Performance data already exists for employee {$data['employee_id']} on {$data['stat_date']}", 'debug');
            }
        }
    }
    
    /**
     * 计算绩效评分
     */
    private function calculatePerformanceScore($data)
    {
        // 简单的绩效评分算法，可以根据实际业务需求调整
        $baseScore = 60; // 基础分
        $taskScore = min($data['completed_tasks'] * 2, 20); // 任务完成加分，最高20分
        $outputScore = min($data['output_quantity'] * 0.1, 15); // 产出加分，最高15分
        $wageScore = min($data['total_wage'] * 0.01, 5); // 工资加分，最高5分
        
        return $baseScore + $taskScore + $outputScore + $wageScore;
    }
    
    /**
     * 计算质量评分
     */
    private function calculateQualityRate($data)
    {
        // 基于报工数据计算质量评分
        // 这里可以根据实际业务逻辑调整，比如：
        // 1. 基于质检合格率
        // 2. 基于返工率
        // 3. 基于客户投诉率
        // 目前使用简单的算法：基于产出数量和工资比例
        
        if ($data['output_quantity'] > 0 && $data['total_wage'] > 0) {
            // 质量评分 = 基础分 + 产出效率加分
            $baseQuality = 85; // 基础质量分
            $efficiencyBonus = min(($data['output_quantity'] / $data['completed_tasks']) * 2, 10); // 效率加分，最高10分
            return min($baseQuality + $efficiencyBonus, 100); // 最高100分
        }
        
        return 85; // 默认质量分
    }
    
    /**
     * 计算效率评分
     */
    private function calculateEfficiencyRate($data)
    {
        // 基于报工数据计算效率评分
        // 效率 = 产出数量 / 完成任务数 * 100
        
        if ($data['completed_tasks'] > 0) {
            $efficiency = ($data['output_quantity'] / $data['completed_tasks']) * 100;
            return min($efficiency, 200); // 最高200%效率
        }
        
        return 100; // 默认100%效率
    }

    /**
     * 成本报表
     */
    public function costReport()
    {
        $startDate = $this->request->get('start_date', date('Y-m-01'));
        $endDate = $this->request->get('end_date', date('Y-m-d'));

        if ($this->request->isAjax()) {
            // 成本汇总数据
            $summary = Db::name('scanwork_order_cost')
                ->where('cost_date', 'between', [strtotime($startDate), strtotime($endDate)])
                ->field('
                    COUNT(*) as total_orders,
                    SUM(material_cost) as total_material_cost,
                    SUM(labor_cost) as total_labor_cost,
                    SUM(overhead_cost) as total_overhead_cost,
                    SUM(total_cost) as total_cost,
                    SUM(revenue) as total_revenue,
                    SUM(profit) as total_profit,
                    AVG(profit_rate) as avg_profit_rate
                ')
                ->find();

            // 成本趋势数据
            $trend = Db::name('scanwork_order_cost')
                ->where('cost_date', 'between', [strtotime($startDate), strtotime($endDate)])
                ->field('
                    DATE(FROM_UNIXTIME(cost_date)) as date,
                    SUM(material_cost) as material_cost,
                    SUM(labor_cost) as labor_cost,
                    SUM(overhead_cost) as overhead_cost,
                    SUM(total_cost) as total_cost
                ')
                ->group('DATE(FROM_UNIXTIME(cost_date))')
                ->order('date', 'asc')
                ->select();

            // 成本结构分析
            $structure = Db::name('scanwork_order_cost')
                ->where('cost_date', 'between', [strtotime($startDate), strtotime($endDate)])
                ->field('
                    SUM(material_cost) as material_cost,
                    SUM(labor_cost) as labor_cost,
                    SUM(overhead_cost) as overhead_cost
                ')
                ->find();
            
            $data = [
                'summary' => $summary,
                'trend' => $trend,
                'structure' => $structure
            ];
            
            return json(['code' => 1, 'data' => $data]);
        }
        return $this->view->fetch();
    }

    /**
     * 添加
     */
    public function add()
    {
        // 获取用户列表作为员工列表
        $employeeList = [];
        $users = Db::name('user')->where('status', 'normal')->select();
        foreach ($users as $user) {
            $employeeList[$user['id']] = $user['nickname'] . ' (' . $user['username'] . ')';
        }
        $this->view->assign('employeeList', $employeeList);
        
        if ($this->request->isPost()) {
            $params = $this->request->post("row/a");
            if ($params) {
                $params = $this->preExcludeFields($params);
                $result = false;
                Db::startTrans();
                try {
                    $result = Db::name('scanwork_employee_performance')->insert($params);
                    Db::commit();
                } catch (ValidateException $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                } catch (PDOException $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                } catch (Exception $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                }
                if ($result !== false) {
                    $this->success();
                } else {
                    $this->error(__('No rows were inserted'));
                }
            }
            $this->error(__('Parameter %s can not be empty', ''));
        }
        return $this->view->fetch();
    }

    /**
     * 编辑
     */
    public function edit($ids = null)
    {
        $row = Db::name('scanwork_employee_performance')->find($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }
        
        // 获取用户列表作为员工列表
        $employeeList = [];
        $users = Db::name('user')->where('status', 'normal')->select();
        foreach ($users as $user) {
            $employeeList[$user['id']] = $user['nickname'] . ' (' . $user['username'] . ')';
        }
        $this->view->assign('employeeList', $employeeList);
        
        if ($this->request->isPost()) {
            $params = $this->request->post("row/a");
            if ($params) {
                $params = $this->preExcludeFields($params);
                $result = false;
                Db::startTrans();
                try {
                    $result = Db::name('scanwork_employee_performance')->where('id', $ids)->update($params);
                    Db::commit();
                } catch (ValidateException $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                } catch (PDOException $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                } catch (Exception $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                }
                if ($result !== false) {
                    $this->success();
                } else {
                    $this->error(__('No rows were updated'));
                }
            }
            $this->error(__('Parameter %s can not be empty', ''));
        }
        $this->view->assign("row", $row);
        return $this->view->fetch();
    }

    /**
     * 删除
     */
    public function del($ids = "")
    {
        if (!$this->request->isPost()) {
            $this->error(__("Invalid parameters"));
        }
        $ids = $ids ? $ids : $this->request->post("ids");
        if (empty($ids)) {
            $this->error(__('Parameter %s can not be empty', 'ids'));
        }
        $pk = Db::name('scanwork_employee_performance')->getPk();
        $list = Db::name('scanwork_employee_performance')->where($pk, 'in', $ids)->select();

        $count = 0;
        Db::startTrans();
        try {
            foreach ($list as $item) {
                $count += Db::name('scanwork_employee_performance')->delete($item[$pk]);
            }
            Db::commit();
        } catch (PDOException $e) {
            Db::rollback();
            $this->error($e->getMessage());
        } catch (Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
        if ($count) {
            $this->success();
        } else {
            $this->error(__('No rows were deleted'));
        }
    }
    
    /**
     * 生成生产效率数据
     */
    private function generateProductionEfficiencyData($startDate, $endDate)
    {
        // 从报工表统计生产效率数据
        $reportData = Db::name('scanwork_report')
            ->alias('r')
            ->join('scanwork_allocation a', 'r.allocation_id = a.id')
            ->where('r.status', 1)
            ->where('FROM_UNIXTIME(r.report_time)', 'between', [$startDate, $endDate])
            ->field('
                DATE(FROM_UNIXTIME(r.report_time)) as stat_date,
                COUNT(*) as completed_tasks,
                SUM(r.quantity) as actual_quantity,
                SUM(r.wage) as total_wage,
                AVG(r.wage/r.quantity) as avg_price
            ')
            ->group('DATE(FROM_UNIXTIME(r.report_time))')
            ->select();
        
        foreach ($reportData as $data) {
            $exists = Db::name('scanwork_production_efficiency')
                ->where('stat_date', $data['stat_date'])
                ->find();
            
            if (!$exists) {
                $planQuantity = $data['actual_quantity'] * 1.1; // 假设计划产量比实际产量多10%
                $qualifiedQuantity = $data['actual_quantity'] * 0.95; // 假设合格率95%
                $defectQuantity = $data['actual_quantity'] - $qualifiedQuantity;
                $outputRate = ($data['actual_quantity'] / $planQuantity) * 100;
                $qualityRate = ($qualifiedQuantity / $data['actual_quantity']) * 100;
                $efficiencyRate = min($outputRate, 100);
                
                Db::name('scanwork_production_efficiency')->insert([
                    'stat_date' => $data['stat_date'],
                    'stat_type' => 'daily',
                    'plan_quantity' => $planQuantity,
                    'actual_quantity' => $data['actual_quantity'],
                    'qualified_quantity' => $qualifiedQuantity,
                    'defect_quantity' => $defectQuantity,
                    'plan_hours' => 8,
                    'actual_hours' => 8,
                    'standard_hours' => 8,
                    'output_rate' => $outputRate,
                    'quality_rate' => $qualityRate,
                    'efficiency_rate' => $efficiencyRate,
                    'labor_productivity' => $data['actual_quantity'] / 8,
                    'on_time_delivery_rate' => 95,
                    'createtime' => time()
                ]);
            }
        }
    }
    
    /**
     * 生成质量分析数据
     */
    private function generateQualityAnalysisData($startDate, $endDate)
    {
        // 从报工表统计质量分析数据
        $reportData = Db::name('scanwork_report')
            ->alias('r')
            ->join('scanwork_allocation a', 'r.allocation_id = a.id')
            ->where('r.status', 1)
            ->where('FROM_UNIXTIME(r.report_time)', 'between', [$startDate, $endDate])
            ->field('
                DATE(FROM_UNIXTIME(r.report_time)) as stat_date,
                a.model_id as product_id,
                COUNT(*) as completed_tasks,
                SUM(r.quantity) as inspection_quantity,
                SUM(r.wage) as total_wage
            ')
            ->group('DATE(FROM_UNIXTIME(r.report_time)), a.model_id')
            ->select();
        
        foreach ($reportData as $data) {
            $exists = Db::name('scanwork_quality_statistics')
                ->where('stat_date', $data['stat_date'])
                ->where('product_id', $data['product_id'])
                ->find();
            
            if (!$exists) {
                $qualifiedQuantity = $data['inspection_quantity'] * 0.95; // 假设合格率95%
                $unqualifiedQuantity = $data['inspection_quantity'] - $qualifiedQuantity;
                $reworkQuantity = $unqualifiedQuantity * 0.3; // 假设30%返工
                $scrapQuantity = $unqualifiedQuantity - $reworkQuantity;
                $passRate = ($qualifiedQuantity / $data['inspection_quantity']) * 100;
                $finalPassRate = (($qualifiedQuantity + $reworkQuantity) / $data['inspection_quantity']) * 100;
                $defectRate = ($unqualifiedQuantity / $data['inspection_quantity']) * 100;
                $reworkRate = ($reworkQuantity / $data['inspection_quantity']) * 100;
                $scrapRate = ($scrapQuantity / $data['inspection_quantity']) * 100;
                
                Db::name('scanwork_quality_statistics')->insert([
                    'stat_date' => $data['stat_date'],
                    'stat_type' => 'daily',
                    'product_id' => $data['product_id'],
                    'inspection_quantity' => $data['inspection_quantity'],
                    'qualified_quantity' => $qualifiedQuantity,
                    'unqualified_quantity' => $unqualifiedQuantity,
                    'rework_quantity' => $reworkQuantity,
                    'scrap_quantity' => $scrapQuantity,
                    'pass_rate' => $passRate,
                    'final_pass_rate' => $finalPassRate,
                    'defect_rate' => $defectRate,
                    'rework_rate' => $reworkRate,
                    'scrap_rate' => $scrapRate,
                    'top_defect_types' => '尺寸偏差,表面缺陷',
                    'quality_cost' => $unqualifiedQuantity * 10,
                    'createtime' => time()
                ]);
            }
        }
    }
    
    /**
     * 生成物料损耗数据
     */
    private function generateMaterialLossData($startDate, $endDate)
    {
        // 获取实际使用的物料（通过分工分配和报工数据关联）
        $usedMaterials = Db::name('scanwork_report')
            ->alias('r')
            ->join('scanwork_allocation a', 'r.allocation_id = a.id')
            ->join('scanwork_order_material om', 'a.order_id = om.order_id')
            ->join('scanwork_material m', 'om.material_id = m.id')
            ->where('r.status', 1)
            ->where('FROM_UNIXTIME(r.report_time)', 'between', [$startDate, $endDate])
            ->where('om.material_id', '>', 0) // 确保有物料需求
            ->field('
                DATE(FROM_UNIXTIME(r.report_time)) as stat_date,
                om.material_id,
                m.name as material_name,
                m.current_price,
                SUM(a.quantity) as planned_quantity,
                SUM(r.quantity) as actual_quantity,
                om.loss_rate as standard_loss_rate
            ')
            ->group('DATE(FROM_UNIXTIME(r.report_time)), om.material_id')
            ->select();
        
        foreach ($usedMaterials as $data) {
            $exists = Db::name('scanwork_material_loss')
                ->where('stat_date', $data['stat_date'])
                ->where('material_id', $data['material_id'])
                ->find();
            
            if (!$exists) {
                $plannedQuantity = $data['planned_quantity'];
                $actualQuantity = $data['actual_quantity'];
                $lossQuantity = max(0, $plannedQuantity - $actualQuantity);
                $lossRate = $plannedQuantity > 0 ? ($lossQuantity / $plannedQuantity) * 100 : 0;
                $standardLossRate = $data['standard_loss_rate'] ?: 3; // 默认3%
                $expectedLoss = $plannedQuantity * $standardLossRate / 100;
                $excessLoss = max(0, $lossQuantity - $expectedLoss);
                $lossCost = $excessLoss * $data['current_price'];
                
                Db::name('scanwork_material_loss')->insert([
                    'stat_date' => $data['stat_date'],
                    'material_id' => $data['material_id'],
                    'planned_quantity' => $plannedQuantity,
                    'actual_quantity' => $actualQuantity,
                    'loss_quantity' => $lossQuantity,
                    'loss_rate' => $lossRate,
                    'standard_loss_rate' => $standardLossRate,
                    'excess_loss' => $excessLoss,
                    'loss_cost' => $lossCost,
                    'loss_reason' => $excessLoss > 0 ? '超标准损耗' : '正常损耗',
                    'createtime' => time()
                ]);
            }
        }
    }
}

